<?php

function build_product_link($product_id){

$sql = "SELECT products.url, navigation.url AS parent_url, navigation.parent_id FROM products
		LEFT JOIN products_to_navigation ON products.id = products_to_navigation.product_id
		LEFT JOIN navigation ON products_to_navigation.navigation_id = navigation.id
		WHERE products.id = '".mysql_real_escape_string($product_id)."'";
$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
$rs = mysql_fetch_assoc($query);

$link = '/'.$rs['parent_url'].'/'.$rs['url'].'.htm';

	if($rs['parent_id'] != '0'){
	// get parent_url
	
	$sql = "SELECT url, parent_id FROM navigation WHERE id = '".$rs['parent_id']."'";
	$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
	$rs = mysql_fetch_assoc($query);
	
	$link = '/'.$rs['url'].$link;
	
		if($rs['parent_id'] != '0'){
		// get parent_url
		
		$sql = "SELECT url, parent_id FROM navigation WHERE id = '".$rs['parent_id']."'";
		$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
		$rs = mysql_fetch_assoc($query);
		
		$link = '/'.$rs['url'].$link;
		
			if($rs['parent_id'] != '0'){
			// get parent_url
			
			$sql = "SELECT url, parent_id FROM navigation WHERE id = '".$rs['parent_id']."'";
			$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
			$rs = mysql_fetch_assoc($query);
			
			$link = '/'.$rs['url'].$link;
			
			}
		}
	}
	
return $link;

}

function build_category_link($category_id){

$sql = "SELECT url, parent_id FROM navigation WHERE id = '".mysql_real_escape_string($category_id)."'";
$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
$rs = mysql_fetch_assoc($query);

$link = '/'.$rs['url'].'.htm';

	if($rs['parent_id'] != '0'){
	
	$sql = "SELECT url, parent_id FROM navigation WHERE id = '".$rs['parent_id']."'";
	$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
	$rs = mysql_fetch_assoc($query);

	$link = '/'.$rs['url'].$link;
	
			if($rs['parent_id'] != '0'){
	
			$sql = "SELECT url, parent_id FROM navigation WHERE id = '".$rs['parent_id']."'";
			$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
			$rs = mysql_fetch_assoc($query);

			$link = '/'.$rs['url'].$link;
			
			
			
			}
	
	}

return $link;

}

function tier_check($category_id){

$tier = 1;

$sql = "SELECT parent_id FROM navigation WHERE id = '".mysql_real_escape_string($category_id)."'";
$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
$rs = mysql_fetch_assoc($query);

	if($rs['parent_id'] != '0'){
	
	$tier = 2;
	
	$sql = "SELECT parent_id FROM navigation WHERE id = '".$rs['parent_id']."'";
	$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
	$rs = mysql_fetch_assoc($query);

			if($rs['parent_id'] != '0'){
			
			$tier = 3;
			
			}
	
	}
	
return $tier;

}

function product_count($category_id){

	if(tier_check($category_id) == 1){
	
	$sql = "SELECT count(id) AS number_of_products FROM products_to_navigation WHERE navigation_id = '".$category_id."'";
	$prod_query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
	$prod_rs = mysql_fetch_assoc($prod_query);
	
	$total_products = $prod_rs['number_of_products'];
	
		$sql = "SELECT id FROM navigation WHERE parent_id = '".$category_id."'";
		$prod_query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
		while($prod_rs = mysql_fetch_assoc($prod_query)){
		
			$sql = "SELECT id FROM navigation WHERE parent_id = '".$prod_rs['id']."'";
			$prod_query_2 = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
			while($prod_rs_2 = mysql_fetch_assoc($prod_query_2)){
		
				$sql = "SELECT count(id) AS number_of_products FROM products_to_navigation WHERE navigation_id = '".$prod_rs_2['id']."'";
				$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
				$rs = mysql_fetch_assoc($query);
				
				$total_products = $total_products + $rs['number_of_products'];
				
			}
		
		}
	
	}
	
	elseif(tier_check($category_id) == 2){
	
	$sql = "SELECT count(id) AS number_of_products FROM products_to_navigation WHERE navigation_id = '".$category_id."'";
	$prod_query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
	$prod_rs = mysql_fetch_assoc($prod_query);
	
	$total_products = $prod_rs['number_of_products'];
	
		$sql = "SELECT id FROM navigation WHERE parent_id = '".$category_id."'";
		$prod_query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
		while($prod_rs = mysql_fetch_assoc($prod_query)){
		
			$sql = "SELECT count(id) AS number_of_products FROM products_to_navigation WHERE navigation_id = '".$prod_rs['id']."'";
			$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
			$rs = mysql_fetch_assoc($query);
			
			$total_products = $total_products + $rs['number_of_products'];
		
		}

	}
	
	else{
	
	$sql = "SELECT count(id) AS number_of_products FROM products_to_navigation WHERE navigation_id = '".$category_id."'";
	$prod_query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
	$prod_rs = mysql_fetch_assoc($prod_query);
	
	$total_products = $prod_rs['number_of_products'];
	
	}
	
return $total_products;

}

function get_children_categories($category_id){

$cats = array($category_id);

$sql = "SELECT id FROM navigation WHERE parent_id = '".mysql_real_escape_string($category_id)."'";
$query = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
$rows = mysql_num_rows($query);

	if($rows > 0){
	
		while($rs = mysql_fetch_assoc($query)){
		$cats[] = $rs['id'];
	
		$sql = "SELECT id FROM navigation WHERE parent_id = '".mysql_real_escape_string($rs['id'])."'";
		$query_1 = mysql_query( $sql ) or die ("Error in query: $sql. " . mysql_error());
		$rows_1 = mysql_num_rows($query_1);
		
			if($rows_1 > 0){
				while($rs_1 = mysql_fetch_assoc($query_1)){
				$cats[] = $rs_1['id'];
				}
			}
		
		}

	}
	
return $cats;

}

function clean_file_name($Raw){
    $Raw = trim(strtolower($Raw));
    $RemoveChars  = array( "([\40])" , "([^a-zA-Z0-9-])", "(-{2,})" );
    $ReplaceWith = array("-", "", "-");
    return preg_replace($RemoveChars, $ReplaceWith, $Raw);
} 

function build_job_link($trade,$area,$id){

$link = '/job/'.clean_file_name($trade).'/'.clean_file_name($area).'/'.$id;

return $link;

}

function build_tradesman_link($company_name,$id){

$link = '/tradesman/'.$id.'/'.clean_file_name($company_name);

return $link;

}

function post_to_facebook($title, $link, $message){

		$data = array( 'message' => $title, 'link' => $link, 'description' => $message, 'access_token' => PAGE_ACCESS_TOKEN);

		// init
		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, 'https://graph.facebook.com/'.PAGE_ID.'/feed');
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
		// execute and close
		$return = curl_exec($ch);
		curl_close($ch);
		// end
		return $return;

}

?>